# encoding=utf-8


# AccessKey ID
# LTAI4GBZatrbo8WN89P9CvS1

# AccessKey Secret
# hDj49vXsoVieCnqZ8xTIHMnn9DRWNx

# consumerGroupId
# DEFAULT_GROUP

# AMQP 服务端订阅
# amqps://1956787957013395.iot-amqp.cn-shanghai.aliyuncs.com

import time
import sys
import hashlib
import hmac
import base64
import stomp
import ssl
import json
import time
import pymysql
def get_conn(host='127.0.0.1', port=3306, user='root', password='123456', db_name='school_project', charset='utf8'):
    """
    获取mysql的连接资源
    :param host: 主机地址 = 'yhni.cn' @type {String}
    :param port: 端口号 = 3306 @type {Int}
    :param user: 用户名 = 'python' @type {String}
    :param password: 密码 = '123456' @type {String}
    :param db_name: 所用数据名字 = 'PYTHON2020' @type {String}
    :param charset:设定字符集  = 'utf8' @type {String}
    :return: mysql connection
    """
    return pymysql.connect(host=host, port=port,
                           user=user, password=password,
                           db=db_name, charset=charset)


def query_data(conn, sql):
    """
    根据SQL语句查询数据并返回结果
    读操作
    :param conn: 数据库连接资源
    :param sql: SQL语句
    :return:list[dict]
    """
    try:
        cursor = conn.cursor(pymysql.cursors.DictCursor)  ##使每行数据以字典的类型返回
        cursor.execute(sql)
        return cursor.fetchall()
    finally:
        conn.close()


def insertMany(conn, sql, data):
    """
    根据SQL语句执行数据操纵并返回结果
    写操作
    :param conn: 数据库连接资源
    :param sql: SQL语句
    :return:不返回内容
    """
    try:
        cursor = conn.cursor()
        cursor.executemany(sql, data)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        conn.close()

def insert_or_update_data(conn, sql):
    """
    根据SQL语句执行数据操纵并返回结果
    写操作
    :param conn: 数据库连接资源
    :param sql: SQL语句
    :return:不返回内容
    """
    try:
        cursor = conn.cursor()
        cursor.execute(sql)
        conn.commit()
    except Exception as error:
        print(error)
        conn.rollback()
    finally:
        conn.close()



# def infuxdb_control(name,value,tip_time):
#     client = InfluxDBClient(host='127.0.0.1', port=8086, username='root', password='123456')
#     json_body = [{
#         "measurement": 'table_test2',
#         # "time": time.time(),
#         "tags": {
#             'name': name
#             },
#         "fields": {
#             'value': value,
#             'tip_time':tip_time
#             }
#     }]
#     client.switch_database('test_db')
#     res = client.write_points(json_body)  # 写入数据，同时创建表
#     print("写入结果为："+str(res))
#     client.close()


def connect_and_subscribe(conn):
    # 参数说明，请参见AMQP客户端接入说明文档。
    accessKey = "LTAI4GBZatrbo8WN89P9CvS1"
    accessSecret = "hDj49vXsoVieCnqZ8xTIHMnn9DRWNx"
    consumerGroupId = "DEFAULT_GROUP"
    # iotInstanceId：购买的实例请填写实例ID，公共实例请填空字符串""。
    iotInstanceId = ""
    clientId = "lxp29303270esp8266"
    # 签名方法：支持hmacmd5、hmacsha1和hmacsha256。
    signMethod = "hmacsha1"
    timestamp = current_time_millis()
    # userName组装方法，请参见AMQP客户端接入说明文档。
    userName = clientId + "|authMode=aksign" + ",signMethod=" + signMethod \
               + ",timestamp=" + timestamp + ",authId=" + accessKey \
               + ",iotInstanceId=" + iotInstanceId \
               + ",consumerGroupId=" + consumerGroupId + "|"
    signContent = "authId=" + accessKey + "&timestamp=" + timestamp
    # 计算签名，password组装方法，请参见AMQP客户端接入说明文档。
    password = do_sign(accessSecret.encode("utf-8"), signContent.encode("utf-8"))

    conn.connect(userName, password, wait=True)
    conn.subscribe(destination='/topic/#', id=1, ack='auto')


class MyListener(stomp.ConnectionListener):
    def __init__(self, conn):
        self.conn = conn

    def on_error(self, headers, message):
        print('received an error "%s"' % message)

    def on_message(self, headers, message):
        # 获取信息
        # print(headers)
        # print('received a message "%s"' % message)
        # {"deviceType":"CustomCategory","iotId":"WZd5lOvQpTRKxuL1YhPM000000","requestId":"123","productKey":"a10R0jb1HhA","gmtCreate":1599583883204,"deviceName":"WZd5lOvQpTRKxuL1YhPM","items":{"Humidity":{"value":88,"time":1599583883211}}}

        message_dict = json.loads(message)
        # 将JSON数据转化为一个dict

        facilitytype = message_dict['deviceType']
        facilityname = message_dict['deviceName']


        item = message_dict["items"]
        a,b = 0,0
        for key, values in item.items():
            a = key
            b = values
        # print(a,b)
        # print(type(b))

        attribution_name = a
        attribution_value = b["value"]
        attribution_time = b["time"]
        print("获取到如下数据:")
        print("设备类型：\t"+str(facilitytype))
        print("设备名称：\t"+str(facilityname))
        print("属性名称：\t"+str(attribution_name))
        print("属性数值：\t"+str(attribution_value))
        print("属性时间：\t"+str(attribution_time))
        print("----------------------------------------------")
        # infuxdb_control(str(attribution_name),str(attribution_value),str(attribution_time))
        conn = get_conn()
        sql = "INSERT INTO a_zucc_school_project_facility VALUES(null ,'{0}','{1}','{2}',{3},'{4}')".format(facilitytype,facilityname,attribution_name,attribution_value,attribution_time)
        insert_or_update_data(conn,sql)

    def on_disconnected(self):
        print('disconnected')
        connect_and_subscribe(self.conn)

    def on_heartbeat_timeout(self):
        print('on_heartbeat_timeout')

    def on_connected(self, headers, body):
        print("successfully connected")


def current_time_millis():
    return str(int(round(time.time() * 1000)))


def do_sign(secret, sign_content):
    m = hmac.new(secret, sign_content, digestmod=hashlib.sha1)
    return base64.b64encode(m.digest()).decode("utf-8")


# 接入域名，请参见AMQP客户端接入说明文档。
conn = stomp.Connection([('1956787957013395.iot-amqp.cn-shanghai.aliyuncs.com', 61614)])
conn.set_ssl(for_hosts=[('1956787957013395.iot-amqp.cn-shanghai.aliyuncs.com', 61614)], ssl_version=ssl.PROTOCOL_TLS)
conn.set_listener('', MyListener(conn))
connect_and_subscribe(conn)

# conn.send(body='1', destination='/topic/test')

time.sleep(10000)
# conn.send(body='2', destination='/topic/test')
# conn.disconnect()